<?php

namespace app\service;

use app\modules\Admin\models\orderModel;
use Illuminate\Database\Capsule\Manager as DB;

class member
{
    public static function level($id, $level, $oper_type = 'admin', $oper_id = 0, $remark='后台管理员操作变更账号等级')
    {
        try {
            DB::beginTransaction();
            $member = \memberModel::where('id', $id)->first();
            if(!$member){
                throw new \Exception("用户不存在或已被锁定");
            }
            $log    = [
                'agent_id'   => $id,
                'event'      => 'level',
                'before'     => $member->level,
                'after'      => $level,
                'remark'     => $remark,
                'oper_type'  => $oper_type,
                'oper_id'    => $oper_id,
                'created_at' => date('Y-m-d H:i:s')
            ];
            \agentlogModel::insert($log);
            if($member->level<$level){
                $member->demotion_lock = 1;
            }
            if($member->level>$level){
                $member->upgrade_lock  = 1;
            }
            $member->level = $level;
            $member->save();
            DB::commit();
            return true;
        } catch (\Exception $e) {
            DB::rollback();
            return false;
        }
    }

    public static function changeParent($id, $parent_member_id, $oper_type = 'admin', $oper_id = 0, $remark='后台管理员操作变更推荐人')
    {
        DB::transaction(function () use ($id, $parent_member_id, $oper_type, $oper_id, $remark) {
            $member = \memberModel::find($id);
            $flag = true;
            if($remark == "后台管理员操作变更推荐人"){
                $flag = false;
            }
            if($member->parent_member_id>0) {
                // 1. 减原上线所有人的团队业绩
                $parents = \memberchildrenModel::where('children_id', $id)->pluck('member_id');
                \memberModel::whereIn('id', $parents)->decrement('team_consume', $member->team_consume);

                // 2. 减原上线所有人的团队业绩单数
                \memberModel::whereIn('id', $parents)->decrement('team_consume_num', $member->team_consume_num);

                // 3. 减原直属上级的直推业绩
                \memberModel::where('id', $member->parent_member_id)->decrement('direct_consume', $member->total_consume);

                // 4. 减原直属上级的直推业绩单数
                \memberModel::where('id', $member->parent_member_id)->decrement('direct_consume_num', $member->total_consume_num);

                // 5. 删除原上线所有人的相关children子线
                $move_team_children = \memberchildrenModel::where('member_id', $member->id)->pluck('children_id');
                \memberchildrenModel::whereIn('member_id', $parents)->where('children_id', $member->id)->delete();
                \memberchildrenModel::whereIn('member_id', $parents)->whereIn('children_id', $move_team_children)->delete();
            }
            // 6. 修改用户推荐人
            $insertRow = [
                'member_id'  => $member->id,
                'event'      => 'change',
                'event_id'   => 0,
                'before_pid' => $member->parent_member_id,
                'after_pid'  => $parent_member_id,
                'remark'     => $remark . ', 会员' . $id . '推荐人更新为' . $parent_member_id,
                'oper_type'  => $oper_type,
                'oper_id'    => $oper_id,
                'created_at' => date('Y-m-d H:i:s')
            ];
            \memberrelationModel::insert($insertRow);
            $member->parent_member_id = $parent_member_id;
            $member->save();

            // 7. 重建新上线所有人的相关children子线
            $parents    = \memberchildrenModel::where('children_id', $parent_member_id)->pluck('member_id', 'level')->toArray();
            $parents[0] = $parent_member_id;
            $children = \memberchildrenModel::where('member_id', $member->id)->get();
            foreach ($parents as $level => $parent) {
                $childRow = [
                    'member_id'   => $parent,
                    'level'       => $level + 1,
                    'children_id' => $member->id,
                ];
                DB::table('member_children')->insertOrIgnore($childRow);
                foreach ($children as $child) {
                    $childRow = [
                        'member_id'   => $parent,
                        'level'       => $level + 1 + $child->level,
                        'children_id' => $child->children_id,
                    ];
                    DB::table('member_children')->insertOrIgnore($childRow);
                }
            }

            // 8. 加新上线所有人的团队业绩
            $newParents = \memberchildrenModel::where('children_id', $member->id)->pluck('member_id');
            \memberModel::whereIn('id', $newParents)->increment('team_consume', $member->team_consume);

            // 9. 加新上线所有人的团队业绩单数
            \memberModel::whereIn('id', $newParents)->increment('team_consume_num', $member->team_consume_num);

            // 10. 加新直属上级的直推业绩
            \memberModel::where('id', $parent_member_id)->increment('direct_consume', $member->total_consume);

            // 11. 加新直属上级的直推业绩单数
            \memberModel::where('id', $parent_member_id)->increment('direct_consume_num', $member->total_consume_num);
            if($flag){
                // 12. 给新直属上级发放推荐奖励
//                $score = \configModel::where('type', 18)->where('key', 'recommend_score')->value('value');
//                \memberModel::score($score, $parent_member_id, 'share', $member->id, '邀请推荐人获得积分' . $score . ', 用户' . $member->id . ':' . $member->nickname);
            }

        });
    }

    public static function cleanParent($id, $oper_type = 'admin', $oper_id = 0, $remark='后台管理员操作清空推荐人')
    {
        DB::transaction(function () use ($id, $oper_type, $oper_id, $remark) {
            $member = \memberModel::find($id);

            if($member->parent_member_id>0) {
                // 1. 减原上线所有人的团队业绩
                $parents = \memberchildrenModel::where('children_id', $id)->pluck('member_id');
                \memberModel::whereIn('id', $parents)->decrement('team_consume', $member->team_consume);

                // 2. 减原上线所有人的团队业绩单数
                \memberModel::whereIn('id', $parents)->decrement('team_consume_num', $member->team_consume_num);

                // 3. 减原直属上级的直推业绩
                \memberModel::where('id', $member->parent_member_id)->decrement('direct_consume', $member->total_consume);

                // 4. 减原直属上级的直推业绩单数
                \memberModel::where('id', $member->parent_member_id)->decrement('direct_consume_num', $member->total_consume_num);

                // 5. 删除原上线所有人的相关children子线
                $move_team_children = \memberchildrenModel::where('member_id', $member->id)->pluck('children_id');
                \memberchildrenModel::whereIn('member_id', $parents)->where('children_id', $member->id)->delete();
                \memberchildrenModel::whereIn('member_id', $parents)->whereIn('children_id', $move_team_children)->delete();
            }
            // 6. 修改用户推荐人
            $insertRow = [
                'member_id'  => $member->id,
                'event'      => 'change',
                'event_id'   => 0,
                'before_pid' => $member->parent_member_id,
                'after_pid'  => 0,
                'remark'     => $remark . ', 会员' . $id . '推荐人更新为' . 0,
                'oper_type'  => $oper_type,
                'oper_id'    => $oper_id,
                'created_at' => date('Y-m-d H:i:s')
            ];
            \memberrelationModel::insert($insertRow);
            $member->parent_member_id = 0;
            $member->save();
        });
    }
    
    /**
     * @param $source_id 源账号ID
     * @param $target_id 合并至账号ID
     * @param string $oper_type 操作员身份 [admin=管理员,system=系统,user=用户]
     * @param int $oper_id 操作员ID
     */
    public static function merge(int $source_id,int $target_id, $oper_type = 'admin', $oper_id = 0, $remark='后台管理员操作账号合并')
    {
        if($source_id==$target_id) {
            throw new \Exception('源账号和目标账号相同', 30001);
        }
        if(\memberchildrenModel::where('children_id', $source_id)->where('member_id', $target_id)->exists()){
            throw new \Exception('源账号和目标账号有上下级关系', 30002);
        }
        if(\memberchildrenModel::where('member_id', $source_id)->where('children_id', $target_id)->exists()){
            throw new \Exception('源账号和目标账号有上下级关系', 30003);
        }
        DB::transaction(function () use ($source_id, $target_id, $oper_type, $oper_id, $remark) {
            $source = \memberModel::find($source_id);
            $target = \memberModel::find($target_id);

            if($source->parent_member_id>0) {
                // 1. 减源账号上线所有人的团队业绩
                $parents = \memberchildrenModel::where('children_id', $source_id)->pluck('member_id');
                \memberModel::whereIn('id', $parents)->decrement('team_consume', $source->team_consume);

                // 2. 减源账号上线所有人的团队业绩单数
                \memberModel::whereIn('id', $parents)->decrement('team_consume_num', $source->team_consume_num);

                // 3. 减源账号直属上级的直推业绩
                \memberModel::where('id', $source->parent_member_id)->decrement('direct_consume', $source->total_consume);

                // 4. 减源账号直属上级的直推业绩单数
                \memberModel::where('id', $source->parent_member_id)->decrement('direct_consume_num', $source->total_consume_num);

                // 5. 删除源账号上线所有人的相关children子线
                $move_team_children = \memberchildrenModel::where('member_id', $source->id)->pluck('children_id');
                \memberchildrenModel::whereIn('member_id', $parents)->where('children_id', $source->id)->delete();
                \memberchildrenModel::whereIn('member_id', $parents)->whereIn('children_id', $move_team_children)->delete();
            }

            #step 6.更新帐号信息
            $target->nickname = $target->nickname ?: $source->nickname;
            $target->realname = $target->realname ?: $source->realname;
            $target->avatar   = $target->avatar ?: $source->avatar;
            $target->email    = $target->email ?: $source->email;
            $target->level    = $target->level > $source->level ? $target->level : $source->level;

            $target->token         = $source->token ?: '';
            // $target->money         = $target->money + $source->money;
            // $target->score         = $target->score + $source->score;
            $target->total_income  = $target->total_income + $source->total_income;
            $target->total_consume = $target->total_consume + $source->total_consume;

            $target->team_consume  = $target->team_consume + $source->team_consume;
            $target->team_consume_num  = $target->team_consume_num + $source->team_consume_num;
            $target->direct_consume  = $target->direct_consume + $source->direct_consume;
            $target->direct_consume_num  = $target->direct_consume_num + $source->direct_consume_num;
            $target->save();

            #step 6.1 余额变动日志
            if ($source->money>0) {
                \memberModel::money($source->money, $target_id, 'merge', 0, '合并账号时, 给目标账号累加余额');
            }

            #step 6.2 积分变动日志
            if ($source->score>0) {
                \memberModel::score($source->score, $target_id, 'merge', 0, '合并账号时, 给目标账号累加积分');
            }

            // 7. 重建目标账号上线所有人的相关children子线
            $parents    = \memberchildrenModel::where('children_id', $target->id)->pluck('member_id', 'level')->toArray();
            $parents[0] = $target->id;
            $children   = \memberchildrenModel::where('member_id', $source->id)->get();
            foreach ($parents as $level => $parent) {
                foreach ($children as $child) {
                    $childRow = [
                        'member_id'   => $parent,
                        'level'       => $level + $child->level,
                        'children_id' => $child->children_id,
                    ];
                    DB::table('member_children')->insert($childRow);
                }
            }

            // 8. 加新上线所有人的团队业绩
            $newParents = \memberchildrenModel::where('children_id', $target->id)->pluck('member_id');
            \memberModel::whereIn('id', $newParents)->increment('team_consume', $source->team_consume);

            // 9. 加新上线所有人的团队业绩单数
            \memberModel::whereIn('id', $newParents)->increment('team_consume_num', $source->team_consume_num);

            // 10. 加新直属上级的直推业绩
            \memberModel::where('id', $target->parent_member_id)->increment('direct_consume', $source->total_consume);

            // 11. 加新直属上级的直推业绩单数
            \memberModel::where('id', $target->parent_member_id)->increment('direct_consume_num', $source->total_consume_num);


            #step 12.代理分红及日志信息
            \agentrewardModel::where('buyer_id', $source->id)->update(['buyer_id' => $target->id]);
            \agentrewardModel::where('agent_id', $source->id)->update(['agent_id' => $target->id]);
            \agentlogModel::where('agent_id', $source->id)->update(['agent_id' => $target->id]);

            #step 13.股东分红及日志信息
            //gudongModel::where('gudong_id', $source->id)->update(['gudong_id' => $target->id]);
            \gudongrewardModel::where('buyer_id', $source->id)->update(['buyer_id' => $target->id]);
            \gudongrewardModel::where('gudong_id', $source->id)->update(['gudong_id' => $target->id]);
            \gudonglogModel::where('gudong_id', $source->id)->update(['gudong_id' => $target->id]);

            #step 14.区代分红及日志信息
            //qudaiModel::where('qudai_id', $source->id)->update(['qudai_id' => $target->id]);
            \qudairewardModel::where('buyer_id', $source->id)->update(['buyer_id' => $target->id]);
            \qudairewardModel::where('qudai_id', $source->id)->update(['qudai_id' => $target->id]);
            \qudailogModel::where('qudai_id', $source->id)->update(['qudai_id' => $target->id]);

            #step 15.推客分红及日志信息
            //tuikeModel::where('tuike_id', $source->id)->update(['tuike_id' => $target->id]);
            \tuikerewardModel::where('buyer_id', $source->id)->update(['buyer_id' => $target->id]);
            \tuikerewardModel::where('tuike_id', $source->id)->update(['tuike_id' => $target->id]);
            \tuikelogModel::where('tuike_id', $source->id)->update(['tuike_id' => $target->id]);

            #step 16.合并订单
            \orderModel::where('member_id', $source->id)->update(['member_id' => $target->id]);
            \orderModel::where('customer_id', $source->id)->update(['customer_id' => $target->id]);
            \orderitemModel::where('member_id', $source->id)->update(['member_id' => $target->id]);
            \orderaftersaleModel::where('member_id', $source->id)->update(['member_id' => $target->id]);
            \orderexpressModel::where('member_id', $source->id)->update(['member_id' => $target->id]);
            \orderexpresslogModel::where('member_id', $source->id)->update(['member_id' => $target->id]);

            #step 17.会员附属表
            \shareModel::where('member_id', $source->id)->update(['member_id' => $target->id]);
            \shareModel::where('share_member_id', $source->id)->update(['share_member_id' => $target->id]);
            \memberaddressModel::where('member_id', $source->id)->update(['member_id' => $target->id]);
            \memberbankModel::where('member_id', $source->id)->update(['member_id' => $target->id]);
            \memberfavoriteModel::where('member_id', $source->id)->update(['member_id' => $target->id]);
            \membercouponModel::where('member_id', $source->id)->update(['member_id' => $target->id]);
            \memberoauthModel::where('member_id', $source->id)->update(['member_id' => $target->id]);
            // \membersignModel::where('member_id', $source->id)->update(['member_id' => $target->id]);
            \membersignupModel::where('member_id', $source->id)->update(['member_id' => $target->id]);
            // \membermoneylogModel::where('member_id', $source->id)->update(['member_id' => $target->id]);
            // \memberscorelogModel::where('member_id', $source->id)->update(['member_id' => $target->id]);
            // \membertransferlogModel::where('member_id', $source->id)->update(['member_id' => $target->id]);
            // \membertransferlogModel::where('target_id', $source->id)->update(['target_id' => $target->id]);
            // \memberviewModel::where('member_id', $source->id)->update(['member_id' => $target->id]);
            \memberwalletapplyModel::where('member_id', $source->id)->update(['member_id' => $target->id]);
            \memberwalletrechargeModel::where('member_id', $source->id)->update(['member_id' => $target->id]);

            #step 18.购物车
            \cartModel::where('member_id', $source->id)->update(['member_id' => $target->id]);

            #step 19.更改源账号直属下级的parent_member_id
            \memberModel::where('parent_member_id', $source->id)->update(['parent_member_id'=>$target->id]);

            #step 20.账号合并日志
            $mergeLog = [
                'event'      => 'merge',
                'event_id'   => 0,
                'source_id'  => $source->id,
                'target_id'  => $target->id,
                'remark'     => $remark,
                'oper_type'  => $oper_type,
                'oper_id'    => $oper_id,
                'created_at' => date('Y-m-d H:i:s')
            ];
            \membermergeModel::insert($mergeLog);

            #step 21.删除source账号
            $source->token         = '';
            $source->save();
            $source->delete();
        });
    }

    /**
     * 个人消费明细
     * @param array $params
     */
    public static function totalConsume($params = [])
    {
        $id           = $params['id'];
        $start_on     = $params['start_on'];
        $end_on       = $params['end_on'];
        $keywordsType = $params['keywordsType'];
        $keywords     = $params['keywords'] ?? '';
        $sort         = $params['sort'] ?? 'created_at';
        $order        = $params['order'] ?? 'desc';
        $page         = $params['page'] ?? 1;
        $limit        = $params['rows'] ?? 10;

        $query = (new \orderModel)->withTrashed()->with('member')->where('status', '>', 0);
        $query = $query->where(function ($query) use ($id) {
            $query->where('customer_id', $id)->orWhere(function ($query) use ($id) {
                $query->where('member_id', $id)->where('customer_id', 0);
            });
        })->whereExists(function ($query) {
            $query->from('order_item')->whereColumn('order_item.order_id', 'order.id')
                ->where('calc_consume', '=', 1)
                ->where('refund_status', '<', 2);
        });

        if (!empty($start_on)) {
            $query = $query->whereDate('created_at', '>=', $start_on);
        }
        if (!empty($end_on)) {
            $query = $query->whereDate('created_at', '<=', $end_on);
        }
        if ($keywords !== '') {
            switch ($keywordsType) {
                case 'order_id':
                    $query = $query->where('id', $keywords);
                    break;
                case 'order_sn':
                    $query = $query->where('order_sn', 'like', "%{$keywords}%");
                    break;
                case 'member_id':
                    $query = $query->where('member_id', $keywords);
                    break;
                case 'member_phone':
                    $query = $query->whereIn('member_id', DB::table('member')->where('phone', 'like', "%{$keywords}%")->limit(20)->pluck('id'));
                    break;
                case 'customer_id':
                    $query = $query->where('customer_id', $keywords);
                    break;
                case 'consignee':
                    $query = $query->where('consignee', $keywords);
                    break;
                case 'consignee_phone':
                    $query = $query->where('phone', 'like', "%{$keywords}%");
                    break;
            }
        }
        $total = $query->count();
        $query = $query->orderBy($sort, $order)->orderBy('id', 'DESC');
        if (!empty($page)) {
            $offset = ($page - 1) * $limit;
            $query  = $query->offset($offset)->limit($limit);
        }
        $rows = $query->get()->append('status_name');
        return ['total' => $total, 'rows' => $rows];
    }

    /**
     * 已退款个人消费明细
     * @param array $params
     */
    public static function totalConsumeRefund($params = [])
    {
        $id           = $params['id'];
        $start_on     = $params['start_on'];
        $end_on       = $params['end_on'];
        $keywordsType = $params['keywordsType'];
        $keywords     = $params['keywords'] ?? '';
        $sort         = $params['sort'] ?? 'created_at';
        $order        = $params['order'] ?? 'desc';
        $page         = $params['page'] ?? 1;
        $limit        = $params['rows'] ?? 10;

        $query = (new \orderModel)->withTrashed()->with('member')->where('status', '>', 0);
        $query = $query->where(function ($query) use ($id) {
            $query->where('customer_id', $id)->orWhere(function ($query) use ($id) {
                $query->where('member_id', $id)->where('customer_id', 0);
            });
        })->whereExists(function ($query) {
            $query->from('order_item')->whereColumn('order_item.order_id', 'order.id')
                ->where('calc_consume', '=', 1)
                ->where('refund_status', '>=', 2);
        });

        if (!empty($start_on)) {
            $query = $query->whereDate('created_at', '>=', $start_on);
        }
        if (!empty($end_on)) {
            $query = $query->whereDate('created_at', '<=', $end_on);
        }
        if ($keywords !== '') {
            switch ($keywordsType) {
                case 'order_id':
                    $query = $query->where('id', $keywords);
                    break;
                case 'order_sn':
                    $query = $query->where('order_sn', 'like', "%{$keywords}%");
                    break;
                case 'member_id':
                    $query = $query->where('member_id', $keywords);
                    break;
                case 'member_phone':
                    $query = $query->whereIn('member_id', DB::table('member')->where('phone', 'like', "%{$keywords}%")->limit(20)->pluck('id'));
                    break;
                case 'customer_id':
                    $query = $query->where('customer_id', $keywords);
                    break;
                case 'consignee':
                    $query = $query->where('consignee', $keywords);
                    break;
                case 'consignee_phone':
                    $query = $query->where('phone', 'like', "%{$keywords}%");
                    break;
            }
        }
        $total = $query->count();
        $query = $query->orderBy($sort, $order)->orderBy('id', 'DESC');
        if (!empty($page)) {
            $offset = ($page - 1) * $limit;
            $query  = $query->offset($offset)->limit($limit);
        }
        $rows = $query->get()->append('status_name');
        return ['total' => $total, 'rows' => $rows];
    }

    /**
     * 团队业绩明细
     * @param array $params
     * @return array
     */
    public static function teamConsume($params = [])
    {
        $id           = $params['id'];
        $start_on     = $params['start_on'];
        $end_on       = $params['end_on'];
        $keywordsType = $params['keywordsType'];
        $keywords     = $params['keywords'] ?? '';
        $sort         = $params['sort'] ?? 'order.created_at';
        $order        = $params['order'] ?? 'desc';
        $page         = $params['page'] ?? 1;
        $limit        = $params['rows'] ?? 10;

        $query    = \orderModel::withTrashed()
            ->join('order_item', 'order.id', '=', 'order_item.order_id')
            ->with('member')
            ->where('order.status', '>', 0)
            ->where('order_item.calc_consume', '=', 1)
            ->where('order_item.refund_status', '<', 2);
        if (!empty($params['only_me'])) {
            $query    = $query->where('order_item.member_id', $id);
        } else {
            $query    = $query->whereRaw("(cs_order_item.member_id={$id} OR cs_order_item.member_id in (SELECT children_id from cs_member_children where `member_id`={$id}))");
        }
        if (!empty($start_on)) {
            $query = $query->whereDate('order.created_at', '>=', $start_on);
        }
        if (!empty($end_on)) {
            $query = $query->whereDate('order.created_at', '<=', $end_on);
        }
        if ($keywords !== '') {
            switch ($keywordsType) {
                case 'order_id':
                    $query = $query->where('order.id', $keywords);
                    break;
                case 'order_sn':
                    $query = $query->where('order.order_sn', 'like', "%{$keywords}%");
                    break;
                case 'member_id':
                    $query = $query->where('order_item.member_id', $keywords);
                    break;
                case 'member_phone':
                    $query = $query->whereIn('order_item.member_id', DB::table('member')->where('phone', 'like', "%{$keywords}%")->limit(20)->pluck('id'));
                    break;
                case 'consignee':
                    $query = $query->where('order.consignee', $keywords);
                    break;
                case 'consignee_phone':
                    $query = $query->where('order.phone', 'like', "%{$keywords}%");
                    break;
            }
        }
        $total = $query->count();
        $total_fee = $query->sum('pay_price');
        $query = $query->orderBy($sort, $order)->orderBy('order_item.id', 'DESC');
        if (!empty($page)) {
            $offset = ($page - 1) * $limit;
            $query  = $query->offset($offset)->limit($limit);
        }
        $rows = $query->select(
            'order.id', 'order_item.id as item_id', 'order.order_sn', 'order.consignee', 'order.address', 'order.phone', 'order.province_name', 'order.city_name', 'order.area_name', 'order.status',
            'order.pay_type', 'order.paytime', 'order.memo', 'order.remark', 'order.created_at',
            'order_item.pay_price', 'order_item.member_id', 'order_item.discount_fee'
        )->get()->append('status_name');
        $footer = [
            ['id'=>'合计:','order_sn'=>'','member'=>'','consignee'=>'','address'=>'','status_name'=>'','pay_price'=>$total_fee,'discount_fee'=>'','pay_type_text'=>'','paytime'=>'','memo'=>'','remark'=>'','created_at'=>'']
        ];
        return ['total' => $total, 'rows' => $rows, 'footer' => $footer, 'amount' => $total_fee];
    }

    /**
     * 已退款团队业绩明细
     * @param array $params
     * @return array
     */
    public static function teamConsumeRefund($params = [])
    {
        $id           = $params['id'];
        $start_on     = $params['start_on'];
        $end_on       = $params['end_on'];
        $keywordsType = $params['keywordsType'];
        $keywords     = $params['keywords'] ?? '';
        $sort         = $params['sort'] ?? 'order.created_at';
        $order        = $params['order'] ?? 'desc';
        $page         = $params['page'] ?? 1;
        $limit        = $params['rows'] ?? 10;

        $query    = \orderModel::withTrashed()
            ->join('order_item', 'order.id', '=', 'order_item.order_id')
            ->with('member')
            ->where('order.status', '>', 0)
            ->where('order_item.calc_consume', '=', 1)
            ->where('order_item.refund_status', '>=', 2);
        if (!empty($params['only_me'])) {
            $query    = $query->where('order_item.member_id', $id);
        } else {
            $query    = $query->whereRaw("(cs_order_item.member_id={$id} OR cs_order_item.member_id in (SELECT children_id from cs_member_children where `member_id`={$id}))");
        }
        if (!empty($start_on)) {
            $query = $query->whereDate('order.created_at', '>=', $start_on);
        }
        if (!empty($end_on)) {
            $query = $query->whereDate('order.created_at', '<=', $end_on);
        }
        if ($keywords !== '') {
            switch ($keywordsType) {
                case 'order_id':
                    $query = $query->where('order.id', $keywords);
                    break;
                case 'order_sn':
                    $query = $query->where('order.order_sn', 'like', "%{$keywords}%");
                    break;
                case 'member_id':
                    $query = $query->where('order_item.member_id', $keywords);
                    break;
                case 'member_phone':
                    $query = $query->whereIn('order_item.member_id', DB::table('member')->where('phone', 'like', "%{$keywords}%")->limit(20)->pluck('id'));
                    break;
                case 'consignee':
                    $query = $query->where('order.consignee', $keywords);
                    break;
                case 'consignee_phone':
                    $query = $query->where('order.phone', 'like', "%{$keywords}%");
                    break;
            }
        }
        $total = $query->count();
        $total_fee = $query->sum('pay_price');
        $query = $query->orderBy($sort, $order)->orderBy('order_item.id', 'DESC');
        if (!empty($page)) {
            $offset = ($page - 1) * $limit;
            $query  = $query->offset($offset)->limit($limit);
        }
        $rows = $query->select(
            'order.id', 'order_item.id as item_id', 'order.order_sn', 'order.consignee', 'order.address', 'order.phone', 'order.province_name', 'order.city_name', 'order.area_name', 'order.status',
            'order.pay_type', 'order.paytime', 'order.memo', 'order.remark', 'order.created_at',
            'order_item.pay_price', 'order_item.member_id', 'order_item.discount_fee'
        )->get()->append('status_name');
        $footer = [
            ['id'=>'合计:','order_sn'=>'','member'=>'','consignee'=>'','address'=>'','status_name'=>'','pay_price'=>$total_fee,'discount_fee'=>'','pay_type_text'=>'','paytime'=>'','memo'=>'','remark'=>'','created_at'=>'']
        ];
        return ['total' => $total, 'rows' => $rows, 'footer' => $footer, 'amount' => $total_fee];
    }

    /**
     * 直推业绩明细
     * @param array $params
     * @return array
     */
    public static function teamConsumeV1($params = [])
    {
        $id           = $params['id'];
        $start_on     = $params['start_on'];
        $end_on       = $params['end_on'];
        $keywordsType = $params['keywordsType'];
        $keywords     = $params['keywords'] ?? '';
        $sort         = $params['sort'] ?? 'created_at';
        $order        = $params['order'] ?? 'desc';
        $page         = $params['page'] ?? 1;
        $limit        = $params['rows'] ?? 10;

        $query    = (new \orderModel)->withTrashed()

            ->join('order_item', 'order.id', '=', 'order_item.order_id')
            ->with('member')
            ->where('order.status', '>', 0)
            ->where('order_item.calc_consume', '=', 1)
            ->where('order_item.refund_status', '<', 2);

        $query    = $query->whereRaw("cs_order_item.member_id in (SELECT children_id from cs_member_children where `level`=1 and `member_id`={$id})");
        if (!empty($start_on)) {
            $query = $query->whereDate('order.created_at', '>=', $start_on);
        }
        if (!empty($end_on)) {
            $query = $query->whereDate('order.created_at', '<=', $end_on);
        }
        if ($keywords !== '') {
            switch ($keywordsType) {
                case 'order_id':
                    $query = $query->where('order.id', $keywords);
                    break;
                case 'order_sn':
                    $query = $query->where('order.order_sn', 'like', "%{$keywords}%");
                    break;
                case 'member_id':
                    $query = $query->where('order_item.member_id', $keywords);
                    break;
                case 'member_phone':
                    $query = $query->whereIn('order_item.member_id', DB::table('member')->where('phone', 'like', "%{$keywords}%")->limit(20)->pluck('id'));
                    break;
                case 'consignee':
                    $query = $query->where('order.consignee', $keywords);
                    break;
                case 'consignee_phone':
                    $query = $query->where('order.phone', 'like', "%{$keywords}%");
                    break;
            }
        }
        $total = $query->count();
        $total_fee = $query->sum('pay_price');
        $query = $query->orderBy($sort, $order)->orderBy('order_item.id', 'DESC');
        if (!empty($page)) {
            $offset = ($page - 1) * $limit;
            $query  = $query->offset($offset)->limit($limit);
        }
        $rows = $query->select(
            'order.id', 'order_item.id as item_id', 'order.order_sn', 'order.consignee', 'order.address', 'order.phone', 'order.province_name', 'order.city_name', 'order.area_name', 'order.status',
            'order.pay_type', 'order.paytime', 'order.memo', 'order.remark', 'order.created_at',
            'order_item.pay_price', 'order_item.member_id', 'order_item.discount_fee'
        )->get()->append('status_name');
        $footer = [
            ['id'=>'合计:','order_sn'=>'','member'=>'','consignee'=>'','address'=>'','status_name'=>'','pay_price'=>$total_fee,'discount_fee'=>'','pay_type_text'=>'','paytime'=>'','memo'=>'','remark'=>'','created_at'=>'']
        ];
        return ['total' => $total, 'rows' => $rows, 'footer' => $footer, 'amount' => $total_fee];
    }

    /**
     * 直推业绩明细已退款
     * @param array $params
     * @return array
     */
    public static function teamConsumeV1Refund($params = [])
    {
        $id           = $params['id'];
        $start_on     = $params['start_on'];
        $end_on       = $params['end_on'];
        $keywordsType = $params['keywordsType'];
        $keywords     = $params['keywords'] ?? '';
        $sort         = $params['sort'] ?? 'created_at';
        $order        = $params['order'] ?? 'desc';
        $page         = $params['page'] ?? 1;
        $limit        = $params['rows'] ?? 10;

        $query    = (new \orderModel)->withTrashed()

            ->join('order_item', 'order.id', '=', 'order_item.order_id')
            ->with('member')
            ->where('order.status', '>', 0)
            ->where('order_item.calc_consume', '=', 1)
            ->where('order_item.refund_status', '>=', 2);

        $query    = $query->whereRaw("cs_order_item.member_id in (SELECT children_id from cs_member_children where `level`=1 and `member_id`={$id})");
        if (!empty($start_on)) {
            $query = $query->whereDate('order.created_at', '>=', $start_on);
        }
        if (!empty($end_on)) {
            $query = $query->whereDate('order.created_at', '<=', $end_on);
        }
        if ($keywords !== '') {
            switch ($keywordsType) {
                case 'order_id':
                    $query = $query->where('order.id', $keywords);
                    break;
                case 'order_sn':
                    $query = $query->where('order.order_sn', 'like', "%{$keywords}%");
                    break;
                case 'member_id':
                    $query = $query->where('order_item.member_id', $keywords);
                    break;
                case 'member_phone':
                    $query = $query->whereIn('order_item.member_id', DB::table('member')->where('phone', 'like', "%{$keywords}%")->limit(20)->pluck('id'));
                    break;
                case 'consignee':
                    $query = $query->where('order.consignee', $keywords);
                    break;
                case 'consignee_phone':
                    $query = $query->where('order.phone', 'like', "%{$keywords}%");
                    break;
            }
        }
        $total = $query->count();
        $total_fee = $query->sum('pay_price');
        $query = $query->orderBy($sort, $order)->orderBy('order_item.id', 'DESC');
        if (!empty($page)) {
            $offset = ($page - 1) * $limit;
            $query  = $query->offset($offset)->limit($limit);
        }
        $rows = $query->select(
            'order.id', 'order_item.id as item_id', 'order.order_sn', 'order.consignee', 'order.address', 'order.phone', 'order.province_name', 'order.city_name', 'order.area_name', 'order.status',
            'order.pay_type', 'order.paytime', 'order.memo', 'order.remark', 'order.created_at',
            'order_item.pay_price', 'order_item.member_id', 'order_item.discount_fee'
        )->get()->append('status_name');
        $footer = [
            ['id'=>'合计:','order_sn'=>'','member'=>'','consignee'=>'','address'=>'','status_name'=>'','pay_price'=>$total_fee,'discount_fee'=>'','pay_type_text'=>'','paytime'=>'','memo'=>'','remark'=>'','created_at'=>'']
        ];
        return ['total' => $total, 'rows' => $rows, 'footer' => $footer, 'amount' => $total_fee];
    }

}

